﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class Plant_PlantQuery : System.Web.UI.Page
{
    SqlHelp help = new SqlHelp();
    CommonMethod comHelp = new CommonMethod();

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            // 页面权限
            comHelp.GetRight(this, new string[] { Session["UserCode"].ToString(), Session["CoCode"].ToString(), 
                "PlantQuery" }, null, new string[] { }, new int[] { }, CommonModel.loginOut1);

            //txtDateFrom.Value = System.DateTime.Now.AddDays(-15).ToString("yyyy-MM-dd");
            //txtDateTo.Value = System.DateTime.Now.AddDays(15).ToString("yyyy-MM-dd");
        }
    }

    #region 重置
    protected void cmdSure_Click(object sender, EventArgs e)
    {
        txtCustomerPO.Text = "";
        txtSoNo.Text = "";
        txtCustomerName.Text = "";
        //txtDateFrom.Value = System.DateTime.Now.AddDays(-15).ToString("yyyy-MM-dd");
        //txtDateTo.Value = System.DateTime.Now.AddDays(15).ToString("yyyy-MM-dd");
    }
    #endregion

    protected void cmdQuery_Click(object sender, EventArgs e)
    {
        int m = Convert.ToInt16(RadType.SelectedValue);
        QueryBind(m);
    }

    #region 换页
    protected void Grv_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        try
        {
            Grv.PageIndex = e.NewPageIndex;
            QueryBind(Convert.ToInt16(RadType.SelectedValue));
        }
        catch
        { }
    }
    #endregion

    #region 跳页
    protected void Grv_RowCommand(object sender, GridViewCommandEventArgs e)
    {
        try
        {
            if (e.CommandName == "NewPage")
            {
                TextBox tb = Grv.BottomPagerRow.FindControl("txtNewPageIndex") as TextBox;
                int pageindex = Convert.ToInt32(tb.Text);
                Grv.PageIndex = pageindex - 1;
                //数据绑定
                QueryBind(Convert.ToInt16(RadType.SelectedValue));
            }
        }
        catch
        {
            CommonMethod.regAlertMessage("输入不合法-Error format!", this);
        }
    }
    #endregion

    #region Bind
    protected void QueryBind(int typeFlag)
    {
        DataTable dt = help.GetDataTable("select * from B_DBLoginSet where DBForFlag='2'");
        DataTable ddtt = new DataTable();
        Grv.DataSource = ddtt;

        if (dt != null && dt.Rows.Count > 0)
        {
            foreach (DataRow rowDB in dt.Rows)
            {
                if (rowDB["DBType"].ToString().Equals("0") && typeFlag==1)
                {
                    SqlHelp helpSql = new SqlHelp(string.Format(CommonModel.sqlConnectString,
                        rowDB["DBServerIP"].ToString(), rowDB["DBName"].ToString(), rowDB["DBLoginName"].ToString(), rowDB["DBLoginPwd"].ToString()));
                    string sql = "select top 1000 * from InfoOfContractForPlant_V where 1=1 " + QueryCondition(typeFlag) + " order by PlantSoNo DESC,PlantLineNo DESC";
                    ddtt = helpSql.GetDataTable(sql);
                }
                else if (rowDB["DBType"].ToString().Equals("1") && typeFlag == 2)
                {
                    SqlHelp helpOle = new SqlHelp(string.Format(CommonModel.oleConnectString,
                        rowDB["DBServerIP"].ToString(), rowDB["DBLoginName"].ToString(), rowDB["DBLoginPwd"].ToString()));
                    //查询NJ数据
                    string strSql = @"SELECT distinct HCPO PlantCustomerPONo,HORD PlantSoNo,HCUST PlantCustomerNo,CNME PlantCustomerName,
                                    LLINE PlantLineNo,LPROD PlantItemNo,SORD PlantWONo,LDESC PlantDescription1,LDESC PlantDescription2,
                                    LQORD PlantOrderQty,LQSHP PlantShippedQTY,LNET PlantValue,HCURR PlantCurrency,
                                    LODTE PlantOrderEnterDate,LRDTE PlantOADate,LSDTE PlantRescheduleDate,SRDTE PlantWOReleaseDate,
                                    LPLIN,LUDTE1,LUDTE2,SCORD,SOLOT,SORD MP64001,CLCPOL PlantLineNoForTranding,
                                    SDDTE PlantGoodReadyDate,SQREQ PlantWOQty,'' PlantWOEntryDate,SQFIN PlantWOFinishedQTY                                    
                                    FROM ERPLXP61F.ECH ch join ERPLXP61F.ECL cl on ch.HORD=cl.LORD 
                                    left join ERPLXP61F.FSO fs on ch.HORD=fs.SCORD and CHAR(LLINE)=CHAR(SOLOT) 
                                    left join ERPLXP61F.RCM rc on HCUST=CCUST where 1=1 "
                        + QueryCondition(typeFlag) + " order by HORD DESC,LLINE";

                    OleDbCommand sqlc = new OleDbCommand(strSql);
                    ddtt = helpOle.GetOLEDataTable(sqlc);
                    //if (ddtt != null && ddtt.Rows.Count > 0)
                    //{
                    //    Log.WritLog("DataRows From DB2: " + ddtt.Rows.Count.ToString(), System.DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss"));
                    //}
                }
            }
        }
        #region 数据绑定

        if (ddtt != null && ddtt.Rows.Count > 0)
        {
            DataTable colDT = help.GetDataTable("select	ColumnType,ColumnName from	B_Roles_Column where BelongToTableName='M_SO_Plant' order by ColumnPosition");
            if (colDT != null && colDT.Rows.Count > 0)
            {
                foreach (DataRow row in colDT.Rows)
                {
                    BoundField bc = new BoundField();
                    bc.DataField = row["ColumnType"].ToString();
                    bc.HeaderText = row["ColumnName"].ToString();
                    //bc.ItemStyle.CssClass = "gvItem";
                    bc.ItemStyle.CssClass = "gvHorizontalAlign";
                    Grv.Columns.Add(bc);
                }
                Grv.DataSource = ddtt;
                Grv.DataBind();
                //赋值Label记录条数
                Label label1 = Grv.BottomPagerRow.FindControl("lblCount") as Label;
                label1.Text = ddtt.Rows.Count.ToString();
                // 设置分页行永远显示
                Grv.BottomPagerRow.Visible = true;
            }
        }
        #endregion
    }
    protected string QueryCondition(int typeFlag)
    {
        string conditionStr = string.Empty;

        if (typeFlag == 1)
        {
            #region SQL
            if (!string.IsNullOrEmpty(txtSoNo.Text))
            {
                conditionStr += string.Format(" and PlantSoNo like '%{0}%'", txtSoNo.Text);
            }
            if (!string.IsNullOrEmpty(txtCustomerPO.Text))
            {
                conditionStr += string.Format(" and PlantCustomerPONo like '%{0}%'", txtCustomerPO.Text);
            }
            if (!string.IsNullOrEmpty(txtCustomerName.Text))
            {
                conditionStr += string.Format(" and PlantCustomerName like '%{0}%'", txtCustomerName.Text);
            }
            if (!ddlSOStatus.SelectedValue.Equals("All"))
            {
                if (ddlSOStatus.SelectedValue.Equals("Close"))
                {
                    conditionStr += @" and (cast(PlantOrderQty as float)-cast(PlantShippedQTY as float))=0 and PlantOrderQty is not null ";
                    conditionStr += " and PlantShippedQTY is not null ";
                }
                else if (ddlSOStatus.SelectedValue.Equals("Open"))
                {
                    conditionStr += @" and (cast(PlantOrderQty as float)-cast(PlantShippedQTY as float))>0 and PlantOrderQty is not null ";
                    conditionStr += " and PlantShippedQTY is not null ";
                }
            }
            #region 时间
            if (!string.IsNullOrEmpty(txtDateFrom.Value) && !string.IsNullOrEmpty(txtDateTo.Value))
            {
                conditionStr += " and convert(datetime,PlantOrderEnterDate) between convert(datetime,'"
                    + txtDateFrom.Value + "')  and  convert(datetime,'" + txtDateTo.Value + "') ";
            }
            else if (!string.IsNullOrEmpty(txtDateFrom.Value))
            {
                conditionStr += " and convert(datetime,PlantOrderEnterDate)>convert(datetime,'" + txtDateFrom.Value + "') ";
            }
            else if (!string.IsNullOrEmpty(txtDateTo.Value))
            {
                conditionStr += " and convert(datetime,PlantOrderEnterDate)<convert(datetime,'" + txtDateTo.Value + "') ";
            }
            #endregion
            #endregion
        }
        else if (typeFlag == 2)
        {
            #region DB2
            if (!string.IsNullOrEmpty(txtSoNo.Text))
            {
                conditionStr += string.Format(" and HORD like '%{0}%'", txtSoNo.Text);
            }
            if (!string.IsNullOrEmpty(txtCustomerPO.Text))
            {
                conditionStr += string.Format(" and HCPO like '%{0}%'", txtCustomerPO.Text);
            }
            if (!string.IsNullOrEmpty(txtCustomerName.Text))
            {
                conditionStr += string.Format(" and CNME like '%{0}%'", txtCustomerName.Text);
            }
            if (!ddlSOStatus.SelectedValue.Equals("All"))
            {
                if (ddlSOStatus.SelectedValue.Equals("Close"))
                {
                    conditionStr += @" and (DECIMAL(LQORD,16,0)-DECIMAL(LQSHP,16,0))=0 and LQORD is not null ";
                    conditionStr += " and LQSHP is not null ";
                }
                else if (ddlSOStatus.SelectedValue.Equals("Open"))
                {
                    conditionStr += @" and (DECIMAL(LQORD,16,0)-DECIMAL(LQSHP,16,0))>0 and LQORD is not null ";
                    conditionStr += " and LQSHP is not null ";
                }
            }
            #region 时间
            if (!string.IsNullOrEmpty(txtDateFrom.Value) && !string.IsNullOrEmpty(txtDateTo.Value))
            {
                conditionStr += " and convert(datetime,LODTE) between convert(datetime,'"
                    + txtDateFrom.Value + "')  and  convert(datetime,'" + txtDateTo.Value + "') ";
            }
            else if (!string.IsNullOrEmpty(txtDateFrom.Value))
            {
                conditionStr += " and convert(datetime,LODTE)>convert(datetime,'" + txtDateFrom.Value + "') ";
            }
            else if (!string.IsNullOrEmpty(txtDateTo.Value))
            {
                conditionStr += " and convert(datetime,LODTE)<convert(datetime,'" + txtDateTo.Value + "') ";
            }
            #endregion
            #endregion
        }

        return conditionStr;
    }
    #endregion
}